package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import util.DBUtil;

import dao.CategoryDAO;
import entity.Category;

public class JdbcCategoryDAO implements CategoryDAO{
	private static final String findByParentId = 
		"select dc.*, "+
		"	count(dcp.product_id) as pnum "+
		"from d_category dc  "+
		"	left outer join d_category_product dcp  "+
		"	on(dc.id=dcp.cat_id) "+
		"where dc.parent_id=? "+
		"group by dc.id "+
		"order by dc.turn ";
	@Override
	public List<Category> findAll() throws Exception {
		List<Category> cates = new ArrayList<Category>();
		Connection conn = DBUtil.getConnction();
		String sql = "select * from d_category";
		PreparedStatement pst = conn.prepareStatement(sql);
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			Category cate = new Category();
			cate.setId(rs.getInt("id"));
			cate.setTurn(rs.getInt("turn"));
			cate.setEnName(rs.getString("en_name"));
			cate.setName(rs.getString("name"));
			cate.setDescription(rs.getString("description"));
			cate.setParentId(rs.getInt("parent_id"));
			cates.add(cate);
		}
		
		return cates;
	}
	
	@Override
	public Category findById(int id) throws Exception {
		Category cate = null;
		Connection conn = DBUtil.getConnction();
		String sql = "select * from d_category where id=?";
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setInt(1, id);
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			cate = new Category();
			cate.setId(rs.getInt("id"));
			cate.setTurn(rs.getInt("turn"));
			cate.setEnName(rs.getString("en_name"));
			cate.setName(rs.getString("name"));
			cate.setDescription(rs.getString("description"));
			cate.setParentId(rs.getInt("parent_id"));
		}
		
		return cate;
	}
	@Override
	public List<Category> findByParentId(int parendId) throws Exception {
		List<Category> cates = new ArrayList<Category>();
		Connection conn = DBUtil.getConnction();
//		String sql = "select * from d_category where parent_id=?";
		PreparedStatement pst = conn.prepareStatement(findByParentId);
		pst.setInt(1, parendId);
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			Category cate = new Category();
			cate.setId(rs.getInt("id"));
			cate.setTurn(rs.getInt("turn"));
			cate.setEnName(rs.getString("en_name"));
			cate.setName(rs.getString("name"));
			cate.setDescription(rs.getString("description"));
			cate.setParentId(rs.getInt("parent_id"));
			cate.setPnum(rs.getInt("pnum"));
			cates.add(cate);
		}
		
		return cates;	
	}
	@Override
	public Map<Integer,String> findBYProId(int id) throws Exception {
		Map<Integer,String> map = new HashMap<Integer,String>();
		Connection conn = DBUtil.getConnction();
		String sql="select dcp.cat_id,c.name from d_category_product dcp join d_category c on  dcp.cat_id=c.id  where product_id=3 order by dcp.cat_id asc";
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setInt(1, id);
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			map.put(rs.getInt(1), rs.getString(2));
		}
		return map;
	}
}
